import math

import MySQL
import Visualization


def origin_sum(date='20160101', group='r'):
    table_name = 'raw_pagecount'
    for hour in range(24):
        sql = 'SELECT SUM(`count`) AS page_count FROM {table_name} WHERE SUBSTRING(`project`, 1, 1)=\'{group}\' AND hour<={hour} AND date=\'{date}\''.format(table_name=table_name, hour=hour, group=group, date=date)
        result = MySQL.query(sql)
        sql = 'SELECT COUNT(*) as size FROM {table_name} WHERE hour<={hour} AND date=\'{date}\''.format(table_name=table_name, hour=hour, date=date)
        size = MySQL.query(sql)
        MySQL.save('aqp_result', {
            'table': table_name,
            'date': date,
            'hour': hour,
            'result': result[0]['page_count'],
            'size': size[0]['size'],
            'type': 'accurate',
            'group': group
        })
    return


def origin_sum_longer(group='r'):
    table_name = 'hour_count'
    sql = 'SELECT * FROM {table_name}'.format(table_name=table_name)
    rows = MySQL.query(sql)
    sums = {}
    group_count = {}
    for i in range(len(rows)):
        row = rows[i]
        key = row['date'] + '_' + '%02d' % row['hour']
        if key not in sums:
            sums[key] = 0
        sums[key] += row['count']
        if row['group'] == group:
            tmp = 0
            for j in range(i + 1):
                if rows[j]['group'] == group:
                    tmp += rows[j]['count']
            group_count[key] = tmp
    for k, v in group_count.items():
        MySQL.save('aqp_result', {
            'table': table_name,
            'date': k.split('_')[0],
            'hour': int(k.split('_')[1]),
            'result': v,
            'size': sums[k],
            'type': 'accurate',
            'group': group
        })


def sample_sum(date='20160101', hour=0, group='r'):
    for no in range(5):
        table_name = 'raw_sample_{date}_{hour}_{no}'.format(
                date=date,
                hour=hour,
                no=no
            )
        sql = 'SELECT SUM(`count`) AS page_count FROM {table_name} WHERE SUBSTRING(`project`, 1, 1)=\'{group}\''.format(table_name=table_name, group=group)
        result = MySQL.query(sql)
        sql = 'SELECT COUNT(*) as size FROM {table_name}'.format(table_name=table_name)
        size = MySQL.query(sql)
        MySQL.save('aqp_result', {
            'table': table_name,
            'date': date,
            'hour': hour,
            'result': result[0]['page_count'],
            'size': size[0]['size'],
            'type': 'approximate',
            'group': group
        })


def normalize():
    sql = 'SELECT * FROM aqp_result WHERE id>29'
    rows = MySQL.query(sql)
    for row in rows:
        result = row['result']
        size = row['size']
        std = 1000000.0 * result / size
        MySQL.update('aqp_result', {
            'std': std
        }, 'id=' + str(row['id']))


def draw():
    sql = 'SELECT * FROM aqp_result WHERE type=\'accurate\' AND `table`=\'hour_count\' ORDER BY id ASC'
    rows = MySQL.query(sql)
    sql = 'SELECT * FROM aqp_result WHERE id=1'
    sample = MySQL.query(sql)[0]['std']
    series = 'Error[SUM]'
    maps = {series: {'x': [], 'y': []}}
    for row in rows:
        x = '0' + str(int(row['date']) % 10000) + '_' + str("%02d" % row['hour'])
        # y = abs(sample - row['std']) * 1.0 / row['std']
        y = (sample - row['std']) * 1.0 / row['std']
        maps[series]['x'].append(x)
        maps[series]['y'].append(y)
    print(maps)
    Visualization.draw(maps, chart_type='line', legend=True, x_label='time', y_label='error proportion', title='Error Variety of Sample')


def draw_error_with_var():
    sql = 'SELECT * FROM aqp_result WHERE type=\'accurate\' AND `table`=\'hour_count\' ORDER BY id ASC'
    rows = MySQL.query(sql)
    sql = 'SELECT * FROM aqp_result WHERE id=1'
    sample = MySQL.query(sql)[0]['std']
    series = 'Error[SUM]'
    maps1 = {series: {'x': [], 'y': []}}
    for row in rows:
        x = '0' + str(int(row['date']) % 10000) + '_' + str("%02d" % row['hour'])
        y = abs((sample - row['std']) * 1.0 / row['std'])
        maps1[series]['x'].append(x)
        maps1[series]['y'].append(y)
    print(maps1)
    sql = 'SELECT * FROM hour_count'
    rows = MySQL.query(sql)
    c = {}
    maps2 = {'var': {'x': [], 'y': []}}
    date = 20160101
    while date <= 20160107:
        for hour in range(24):
            for row in rows:
                if row['date'] == str(date) and row['hour'] == hour:
                    if row['group'] not in c:
                        c[row['group']] = 0
                    c[row['group']] += row['count']
            sum_count = 0
            n = 0
            for k, v in c.items():
                n += 1
                sum_count += v
            avg = 1.0 / len(list(c.keys()))
            var = 0
            for k, v in c.items():
                var = var + (v / sum_count - avg) * (v / sum_count - avg)
            var = var / n
            maps2['var']['x'].append('0' + str(date % 10000) + '_' + str("%02d" % hour))
            maps2['var']['y'].append(var)
        date += 1
    print(maps2)
    Visualization.draw_double(maps1, maps2, title='Error[SUM] with Variance in 7 days')


def draw_error_with_skewness():
    sql = 'SELECT * FROM aqp_result WHERE type=\'accurate\' AND `table`=\'hour_count\' ORDER BY id ASC'
    rows = MySQL.query(sql)
    sql = 'SELECT * FROM aqp_result WHERE id=1'
    sample = MySQL.query(sql)[0]['std']
    series = 'Error[SUM]'
    maps1 = {series: {'x': [], 'y': []}}
    for row in rows:
        x = '0' + str(int(row['date']) % 10000) + '_' + str("%02d" % row['hour'])
        y = abs((sample - row['std']) * 1.0 / row['std'])
        maps1[series]['x'].append(x)
        maps1[series]['y'].append(y)
    print(maps1)
    sql = 'SELECT * FROM hour_count'
    rows = MySQL.query(sql)
    c = {}
    maps2 = {'skewness': {'x': [], 'y': []}}
    date = 20160101
    while date <= 20160107:
        for hour in range(24):
            for row in rows:
                if row['date'] == str(date) and row['hour'] == hour:
                    if row['group'] not in c:
                        c[row['group']] = 0
                    c[row['group']] += row['count']
            sum_count = 0
            for k, v in c.items():
                sum_count += v
            avg = 1.0 / len(list(c.keys()))
            skewness = 0
            for k, v in c.items():
                skewness = skewness + (v / sum_count - avg) * (v / sum_count - avg) * (v / sum_count - avg)
            skewness = skewness / len(list(c.keys()))
            maps2['skewness']['x'].append('%02d' % (date % 100) + '_' + '%02d' % hour)
            maps2['skewness']['y'].append(skewness)
        date += 1
    print(maps2)
    Visualization.draw_double(maps1, maps2, title='Error[SUM] with Skewness in 7 days')


if __name__ == '__main__':
    MySQL.init('wiki')
    # sample_sum()
    # origin_sum()
    # normalize()
    # origin_sum_longer('r')
    # draw()
    # draw_error_with_var()
    draw_error_with_skewness()